﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using CSW.Framework.Common;
using CSW.Framework.Common.Data;
using CSW.Framework.Linq.QueryModel;
using CSW.Framework.Linq.SchemaModel;
using Container = CSW.Framework.Linq.QueryModel.Container;
using IEnumerable = System.Collections.IEnumerable;

namespace CSW.Framework.Linq
{
    /// <summary>
    /// Table Query Provider.
    /// </summary>
    /// <typeparam name="T">The table type.</typeparam>
    public class TableQueryProvider<T> : ITableQueryProvider<T>
        where T : class, IBusinessObjectLinq, new()
    {
        private static readonly Database m_Schema;
        private static readonly string m_ConnectionString;
        private static readonly Table m_Table;

        private Container m_MainContainer;
        private Query m_DynamicQuery;
        //private readonly Stack<Container> m_ContainerStack = new Stack<Container>();
        private readonly List<KeyValuePair<string, Func<object>>> m_Funclets;

        private bool m_IsSelect;
        private bool m_InAny;

        static TableQueryProvider()
        {
            const BindingFlags bf = BindingFlags.Public | BindingFlags.Static;
            Type t = typeof(T);
            MethodInfo getSchemaMethod;
            do
            {
                getSchemaMethod = t.GetMethod("GetSchema", bf);
                if (getSchemaMethod == null)
                    t = t.BaseType;
            } while (getSchemaMethod == null && t != null);

            if (getSchemaMethod == null)
                throw new InvalidOperationException(string.Format("Type '{0}' does not implement a static method 'Database GetSchema()'.", typeof(T).FullName));

            MethodInfo getConnectionStringMethod = t.GetMethod("GetConnectionString", bf);
            if (getConnectionStringMethod == null)
                throw new InvalidOperationException(string.Format("Type '{0}' does not implement a static method 'string GetConnectionString()'.", typeof(T).FullName));

            m_Schema = (Database)getSchemaMethod.Invoke(null, null);
            m_ConnectionString = (string)getConnectionStringMethod.Invoke(null, null);

            string typeName = typeof(T).FullName;
            foreach (Table table in m_Schema.Tables)
            {
                if (table.FullClassName == typeName)
                {
                    m_Table = table;
                    break;
                }
            }

            if (m_Table == null)
            {
                throw new Exception(string.Format("Table for type '{0}' not found in schema.", typeName));
            }
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="TableQueryProvider&lt;T&gt;"/> class.
        /// </summary>
        public TableQueryProvider()
        {
            m_DynamicQuery = new Query(m_Schema, m_Table);

            m_MainContainer = m_DynamicQuery.MainContainer;
            m_Funclets = new List<KeyValuePair<string, Func<object>>>();

            //ObjectCounter.Add(this);
            //m_ContainerStack.Push(m_MainContainer);
        }

        /// <summary>
        /// Releases unmanaged resources and performs other cleanup operations before the
        /// <see cref="TableQueryProvider&lt;T&gt;"/> is reclaimed by garbage collection.
        /// </summary>
        ~TableQueryProvider()
        {
            //ObjectCounter.Remove(this);
            Dispose();
        }

        private void Dispose()
        {
            //m_ContainerStack.Clear();
            m_Funclets.Clear();
            m_MainContainer = null;
            m_DynamicQuery = null;
        }

        /// <summary>
        /// Sets the projection level.
        /// </summary>
        /// <param name="levels">The number of levels (default = 1).</param>
        public ITableQueryProvider<T> SetProjectionLevel(int levels)
        {
            m_DynamicQuery.SetProjectionLevel(levels);
            return this;
        }

        /// <summary>
        /// Runs the query with (nolock) hint.
        /// </summary>
        public ITableQueryProvider<T> NoLock()
        {
            m_DynamicQuery.SetNoLock(true);
            return this;
        }

        /// <summary>
        /// Excludes a field from being brought back from the database.
        /// </summary>
        /// <param name="field">The field to exclude.</param>
        public ITableQueryProvider<T> ExcludeField<TResult>(Expression<Func<T, TResult>> field)
        {
            m_DynamicQuery.ExcludeField(field);
            return this;
        }

        /*private Container CurrentMainContainer
        {
            get { return m_ContainerStack.Peek(); }
        }*/

        private void ConcatBinary(Expression left, string right, string op)
        {
            ExpandExpression(left);
            m_DynamicQuery.AddWhere(op);

            string valueName = string.Format("@p{0}", m_Funclets.Count);
            m_DynamicQuery.AddWhere(valueName);

            KeyValuePair<string, Func<object>> kvp = new KeyValuePair<string, Func<object>>(valueName, () => right);
            m_Funclets.Add(kvp);
        }

        private void ConcatBinary(Expression left, Expression right, string op)
        {
            bool addParen = (op == "OR" || op == "AND");

            if (addParen) m_DynamicQuery.AddWhere("(");
            bool wasExists = ExpandExpression(left);
            if (addParen) m_DynamicQuery.AddWhere(")");
            if (wasExists)
            {
                if (op == "=" && right.ToString() == "False")
                {
                    m_DynamicQuery.ChangeLast("(EXISTS(", "(NOT EXISTS(");
                    return;
                }
            }

            m_DynamicQuery.AddWhere(op);
            if (addParen) m_DynamicQuery.AddWhere("(");
            ExpandExpression(right);
            if (addParen) m_DynamicQuery.AddWhere(")");
        }

        /// <summary>
        /// Constructs an <see cref="T:System.Linq.IQueryable"/> object that can evaluate the query represented by a specified expression tree.
        /// </summary>
        /// <param name="expression">An expression tree that represents a LINQ query.</param>
        /// <returns>An <see cref="T:System.Linq.IQueryable"/> that can evaluate the query represented by the specified expression tree.</returns>
        public IQueryable CreateQuery(Expression expression)
        {
            return CreateQuery<T>(expression);
        }

        /// <summary>
        /// Constructs an <see cref="System.Linq.IQueryable&lt;TElement&gt;"/> object that can evaluate the query represented by a specified expression tree.
        /// </summary>
        /// <typeparam name="TElement">The type of the elements of the <see cref="System.Linq.IQueryable&lt;TElement&gt;"/> that is returned.</typeparam>
        /// <param name="expression">An expression tree that represents a LINQ query.</param>
        /// <returns>An <see cref="System.Linq.IQueryable&lt;T&gt;"/> that can evaluate the query represented by the specified expression tree.</returns>
        public IQueryable<TElement> CreateQuery<TElement>(Expression expression)
        {
            ExpressionType nodeType = expression.NodeType;
            switch (nodeType)
            {
                case ExpressionType.Call:
                    MethodCallExpression m = (MethodCallExpression)expression;
                    string methodName = m.Method.Name;
                    LambdaExpression lambda;
                    switch (methodName)
                    {
                        case "Select":
                            m_DynamicQuery.SetMode(QueryElementType.Select);

                            m_IsSelect = true;
                            lambda = (LambdaExpression)((UnaryExpression)m.Arguments[1]).Operand;
                            ExpandExpression(lambda.Body);

                            return new TableQueryObjectProvider<T, TElement>(expression, this);

                        case "Where":
                            if (m_DynamicQuery.HasWhere())
                                m_DynamicQuery.AddWhere("AND");

                            m_DynamicQuery.SetMode(QueryElementType.Where);

                            m_DynamicQuery.AddWhere("(");
                            m_IsSelect = false;
                            lambda = (LambdaExpression)((UnaryExpression)m.Arguments[1]).Operand;
                            ExpandExpression(lambda.Body);
                            m_DynamicQuery.AddWhere(")");

                            return (IQueryable<TElement>)this;

                        case "Take":
                            m_DynamicQuery.SetMode(QueryElementType.Take);

                            m_IsSelect = false;
                            ExpandExpression(m.Arguments[1]);
                            return (IQueryable<TElement>)this;

                        case "OrderBy":
                        case "ThenBy":
                            m_DynamicQuery.SetMode(QueryElementType.OrderBy);

                            m_IsSelect = false;
                            lambda = (LambdaExpression)((UnaryExpression)m.Arguments[1]).Operand;
                            ExpandExpression(lambda.Body);
                            return (IOrderedQueryable<TElement>)this;

                        case "OrderByDescending":
                        case "ThenByDescending":
                            m_DynamicQuery.SetMode(QueryElementType.OrderByDescending);

                            m_IsSelect = false;
                            lambda = (LambdaExpression)((UnaryExpression)m.Arguments[1]).Operand;
                            ExpandExpression(lambda.Body);
                            return (IOrderedQueryable<TElement>)this;

                        case "Join":
                            throw new NotSupportedException(string.Format("Queries using '{0}' are not supported for this collection.", methodName));

                        default:
                            throw new NotSupportedException(string.Format("Queries using '{0}' are not supported for this collection.", methodName));
                    }

                default:
                    throw new NotSupportedException(string.Format("Creating a query from an expression of type '{0}' is not supported.", nodeType));
            }
        }

        private TParamType[] EvaluateFunclets<TParamType>()
            where TParamType : DbParameter, new()
        {
            if (m_Funclets == null)
                return new TParamType[0];

            TParamType[] sqlParams = new TParamType[m_Funclets.Count];

            int index = 0;
            foreach (KeyValuePair<string, Func<object>> item in m_Funclets)
            {
                sqlParams[index] = new TParamType();
                sqlParams[index].ParameterName = string.Format("@p{0}", index);
                object value = item.Value();
                if (value is IBusinessObjectLinq)
                {
                    IBusinessObjectLinq busObj = (IBusinessObjectLinq)value;
                    // ReSharper disable AccessToModifiedClosure
                    Table table = m_Schema.Tables.Where(p => p.FullClassName == value.GetType().FullName).Single();
                    // ReSharper restore AccessToModifiedClosure
                    if (table.PrimaryKey.Length == 1)
                    {
                        TableProperty pkProperty = table.Properties.Where(p => p.Column == table.PrimaryKey[0] && p.IsPrimitiveType).Single();
                        string propertyName = pkProperty.PropertyName;
                        value = busObj.Properties[propertyName].UnsafeValue;
                    }
                }

                sqlParams[index].Value = value ?? DBNull.Value;

                index++;
            }

            return sqlParams;
        }

        /// <summary>
        /// Executes the query represented by a specified expression tree.
        /// </summary>
        /// <param name="expression">An expression tree that represents a LINQ query.</param>
        /// <returns>The value that results from executing the specified query.</returns>
        public object Execute(Expression expression)
        {
            return Execute<T>(expression);
        }

        /// <summary>
        /// Executes the strongly-typed query represented by a specified expression tree.
        /// </summary>
        /// <typeparam name="TResult">The type of the value that results from executing the query.</typeparam>
        /// <param name="expression">An expression tree that represents a LINQ query.</param>
        /// <returns>The value that results from executing the specified query.</returns>
        public TResult Execute<TResult>(Expression expression)
        {
            MethodCallExpression m = (MethodCallExpression)expression;
            string methodName = m.Method.Name;

            object value;
            TResult result;
            T[] tArray;

            switch (methodName)
            {
                case "Single":
                    m_DynamicQuery.SetTakeForSingleCall(2);
                    tArray = this.ToArray();
                    if (tArray.Length == 0)
                        throw new Exception("No records returned from query.");
                    else if (tArray.Length != 1)
                        throw new Exception("More than one record returned from query.");
                    value = tArray[0];
                    result = (TResult)value;
                    return result;

                case "SingleOrDefault":
                    m_DynamicQuery.SetTakeForSingleCall(2);
                    tArray = this.ToArray();
                    if (tArray.Length == 0)
                        value = null;
                    else if (tArray.Length != 1)
                        throw new Exception("More than one record returned from query.");
                    else
                        value = tArray[0];
                    result = (TResult)value;
                    return result;

                case "Any":
                    m_DynamicQuery.SetTakeForSingleCall(1);
                    return (TResult)(object)(this.ToArray().Length > 0);

                default:
                    throw new NotSupportedException(string.Format("Method '{0}' is not supported.", m.Method.Name));
            }
        }

        private void ExpandBinary(BinaryExpression b, string op)
        {
            ConcatBinary(b.Left, b.Right, op);
        }

        private bool ExpandCall(MethodCallExpression m)
        {
            string methodName = m.Method.Name;
            string newValue;

            switch (methodName)
            {
                case "Any":
                    if (m_InAny)
                        throw new NotSupportedException("Nested 'Any' not supported.");

                    m_InAny = true;
                    m_DynamicQuery.AddWhere(" (EXISTS( SELECT NULL AS [EMPTY] FROM ");
                    ExpandExpression(m.Arguments[0]);
                    m_DynamicQuery.AddWhere(")) ");
                    m_InAny = false;
                    return true;

                case "Where":
                    if (!m_InAny)
                        throw new NotSupportedException("'Where' call not inside an 'Any' is not supported.");

                    MemberExpression memberEx = (MemberExpression)m.Arguments[0];
                    LambdaExpression lambda = (LambdaExpression)m.Arguments[1];

                    ExpandExpression(memberEx);
                    ExpandExpression(lambda.Body);
                    break;

                case "LikeString":
                    ConcatBinary(m.Arguments[0], m.Arguments[1], "LIKE");
                    break;

                case "StartsWith":
                    string startsValue = GetStringValue(m.Arguments[0]);
                    newValue = string.Format("{0}%", startsValue);
                    ConcatBinary(m.Object, newValue, "LIKE");
                    break;

                case "EndsWith":
                    string endsValue = GetStringValue(m.Arguments[0]);
                    newValue = string.Format("%{0}", endsValue);
                    ConcatBinary(m.Object, newValue, "LIKE");
                    break;

                case "Contains":
                    string containsValue = GetStringValue(m.Arguments[0]);
                    newValue = string.Format("%{0}%", containsValue);
                    ConcatBinary(m.Object, newValue, "LIKE");
                    break;

                default:
                    throw new NotSupportedException(string.Format("Using method '{0}' in a filter expression is not supported.", methodName));
            }

            return false;
        }

        private static string GetStringValue(Expression expression)
        {
            if (expression is ConstantExpression)
            {
                ConstantExpression contains = (ConstantExpression)expression;
                return string.Format("{0}", contains.Value);
            }
            else if (expression is UnaryExpression)
            {
                UnaryExpression unary = (UnaryExpression)expression;
                MemberExpression member = (MemberExpression)unary.Operand;
                return string.Format("{0}", LinqToSqlHelper.GetMemberExpressionValue(member));
            }
            else if (expression is MemberExpression)
            {
                MemberExpression member = (MemberExpression)expression;
                return string.Format("{0}", LinqToSqlHelper.GetMemberExpressionValue(member));
            }
            else
            {
                throw new NotImplementedException();
            }
        }

        private void ExpandConstant(ConstantExpression c)
        {
            object value = c.Value;
            if (value == null)
            {
                m_DynamicQuery.AddWhere("NULL");
                return;
            }

            Type valueType = value.GetType();
            if (valueType == typeof(string))
            {
                string strValue = "'" + string.Format("{0}", value).Replace("'", "''") + "'";
                m_DynamicQuery.AddWhere(strValue);
            }
            else if (valueType == typeof(int))
            {
                m_DynamicQuery.AddWhere((int)value);
            }
            else if (valueType == typeof(DateTime))
            {
                string strValue = "'" + ((DateTime)value).ToString("yyyy-MM-dd") + "'";
                m_DynamicQuery.AddWhere(strValue);
            }
            else if (valueType == typeof(bool))
            {
                string strValue = (bool)value ? "1" : "0";
                m_DynamicQuery.AddWhere(strValue);
            }
            else
            {
                m_DynamicQuery.AddWhere(value.ToString());
            }
        }

        private bool ExpandExpression(Expression e)
        {
            switch (e.NodeType)
            {
                case ExpressionType.And:
                case ExpressionType.AndAlso:
                    ExpandBinary((BinaryExpression)e, "AND");
                    break;

                case ExpressionType.Call:
                    return ExpandCall((MethodCallExpression)e);

                case ExpressionType.Convert:
                    ExpandUnary((UnaryExpression)e, "");
                    break;

                case ExpressionType.Constant:
                    ExpandConstant((ConstantExpression)e);
                    break;

                case ExpressionType.Equal:
                    ExpandBinary((BinaryExpression)e, "=");
                    break;

                case ExpressionType.GreaterThan:
                    ExpandBinary((BinaryExpression)e, ">");
                    break;

                case ExpressionType.GreaterThanOrEqual:
                    ExpandBinary((BinaryExpression)e, ">=");
                    break;

                case ExpressionType.LessThan:
                    ExpandBinary((BinaryExpression)e, "<");
                    break;

                case ExpressionType.LessThanOrEqual:
                    ExpandBinary((BinaryExpression)e, "<=");
                    break;

                case ExpressionType.MemberAccess:
                    ExpandMemberAccess((MemberExpression)e);
                    break;

                case ExpressionType.Not:
                    ExpandUnary((UnaryExpression)e, "NOT");
                    break;

                case ExpressionType.NotEqual:
                    ExpandBinary((BinaryExpression)e, "!=");
                    break;

                case ExpressionType.Or:
                case ExpressionType.OrElse:
                    ExpandBinary((BinaryExpression)e, "OR");
                    break;

                case ExpressionType.New:
                    NewExpression newEx = (NewExpression)e;
                    foreach (Expression expression in newEx.Arguments)
                    {
                        ExpandExpression(expression);
                    }
                    break;

                case ExpressionType.Parameter:
                    ParameterExpression paramEx = (ParameterExpression)e;
                    if (paramEx.Type.FullName == m_Table.FullClassName)
                    {
                        foreach (TableColumn tableColumn in m_Table.Columns)
                        {
                            m_DynamicQuery.AddProperty(m_MainContainer, tableColumn, null);
                        }

                        if (m_DynamicQuery.GetMode() == QueryElementType.Where)
                        {
                            if (m_Table.PrimaryKey.Length == 1)
                            {
                                string columnName = m_Table.PrimaryKey[0].ColumnName;
                                m_DynamicQuery.AddWhere(string.Format("T0.[{0}]", columnName));
                            }
                            else
                            {
                                throw new Exception("Multi PK not implemented.");
                            }
                        }
                    }
                    else
                    {
                        throw new NotSupportedException(string.Format("Expressions of type '{0}' are not supported.", e.NodeType));
                    }
                    break;

                case ExpressionType.Conditional:
                    ConditionalExpression condEx = (ConditionalExpression)e;
                    ExpandExpression(condEx.Test);
                    throw new NotSupportedException(string.Format("Expressions of type '{0}' are not supported.", e.NodeType));

                default:
                    throw new NotSupportedException(string.Format("Expressions of type '{0}' are not supported.", e.NodeType));
            }

            return false;
        }

        private void ExpandMemberAccess(MemberExpression m)
        {
            MemberInfo member = m.Member;
            Expression e = m.Expression;
            ExpressionType? nodeType = (e == null ? (ExpressionType?)null : e.NodeType);

            bool isConstantExpression = false;
            MemberExpression testMemEx = e as MemberExpression;
            while (testMemEx != null)
            {
                if (testMemEx.Expression is ConstantExpression)
                {
                    isConstantExpression = true;
                    break;
                }

                testMemEx = testMemEx.Expression as MemberExpression;
            }

            if (isConstantExpression)
            {
                // TODO: refactor, duplicated code below
                string valueName = string.Format("@p{0}", m_Funclets.Count);
                MemberTypes memberType = member.MemberType;

                m_DynamicQuery.AddWhere(valueName);

                Func<object> funclet;
                switch (memberType)
                {
                    case MemberTypes.Field:
                        FieldInfo f = (FieldInfo)member;
                        ConstantExpression c = (ConstantExpression)e;
                        funclet = (() => f.GetValue(c.Value));
                        break;

                    case MemberTypes.Property:
                        //PropertyInfo propInfo = (PropertyInfo)member;
                        //MemberExpression c3 = (MemberExpression)e;
                        funclet = () => LinqToSqlHelper.GetMemberExpressionValue(m);
                        break;

                    default:
                        throw new NotSupportedException(string.Format("Accessing member of type '{0}' is not supported.", memberType));
                }

                KeyValuePair<string, Func<object>> kvp = new KeyValuePair<string, Func<object>>(valueName, funclet);
                m_Funclets.Add(kvp);

                return;
            }

            // expirmental
            if (e == null)
            {
                string valueName = string.Format("@p{0}", m_Funclets.Count);
                MemberTypes memberType = member.MemberType;

                m_DynamicQuery.AddWhere(valueName);

                Func<object> funclet;
                switch (memberType)
                {
                    case MemberTypes.Property:
                        PropertyInfo propInfo = (PropertyInfo)member;
                        funclet = (() => propInfo.GetGetMethod(false).Invoke(null, null));
                        break;

                    default:
                        throw new NotSupportedException(string.Format("Accessing member of type '{0}' is not supported.", memberType));
                }

                KeyValuePair<string, Func<object>> kvp = new KeyValuePair<string, Func<object>>(valueName, funclet);
                m_Funclets.Add(kvp);
                return;
            }
            // end experimental

            if (nodeType == ExpressionType.Parameter)
            {
                Type declaringType = member.DeclaringType;
                string memberName = member.Name;

                Table table = m_Schema.Tables.Where(p => p.FullClassName == declaringType.FullName).Single();
                TableProperty tableProperty = table.Properties.Where(p => p.PropertyName == memberName).SingleOrDefault();
                if (tableProperty == null)
                    throw new Exception(string.Format("Property '{0}' not found in table '{1}'.", memberName, table));

                if (!tableProperty.IsChildContainer)
                {
                    TableColumn column = tableProperty.Column;
                    if (column == null)
                        throw new Exception(string.Format("Property '{0}' in table '{1}' does not have an associated column.", memberName, table));

                    Property property;
                    if (table == m_Table)
                    {
                        property = m_DynamicQuery.AddProperty(m_MainContainer, column, nodeType);

                        if (!m_IsSelect)
                        {
                            m_DynamicQuery.AddWhere(property);
                        }
                    }
                    else
                    {
                        // TODO: hackhackhack
                        m_DynamicQuery.AddWhere(string.Format(" AND S1.[{0}] ", column.ColumnName));
                        //Table expressionTable = m_Schema.Tables.Where(p => p.FullClassName == e.Type.FullName).Single();
                        //string sourceColumnName = expressionTable.Columns.Where(p => p.FKTable == table).Single().ColumnName;
                        //throw new NotImplementedException();
                    }
                }
                else
                {
                    if (table != m_Table)
                        throw new NotImplementedException();

                    Table childTable = m_Schema.Tables.Where(p => p.FullClassName == tableProperty.ChildDataType).Single();
                    string childColumnName = childTable.Columns.Where(p => p.FKTable == table).Single().ColumnName;
                    TableProperty mainTableProperty = table.Properties.Where(p => p.Column != null && (p.Column.IsKey)).Single();

                    string where;
                    if (m_Schema.Provider == "SQL Server")
                        where = string.Format("[{0}].[{1}] S1 WHERE S1.[{2}] = {3}.[{4}]", childTable.Schema, childTable.TableName, childColumnName, "T0"/*m_MainContainer.TableAlias*/, mainTableProperty.Column.ColumnName);
                    else if (m_Schema.Provider == "SQL Server CE")
                        where = string.Format("[{0}] S1 WHERE S1.[{1}] = {2}.[{3}]", childTable.TableName, childColumnName, "T0"/*m_MainContainer.TableAlias*/, mainTableProperty.Column.ColumnName);
                    else
                        throw new Exception("Unknown provider");
                    m_DynamicQuery.AddWhere(where);
                }
            }
            else if (nodeType == ExpressionType.MemberAccess)
            {
                MemberExpression memberExpression = (MemberExpression)e;

                Type declaringType = member.DeclaringType;
                string memberName = member.Name;

                Table table = m_Schema.Tables.Where(p => p.FullClassName == declaringType.FullName).SingleOrDefault();

                // This seems hackish.. if the declaring type isn't one of our tables,
                // defer to funclet
                if (table == null)
                {
                    // TODO: refactor, duplicated code below
                    string valueName = string.Format("@p{0}", m_Funclets.Count);
                    MemberTypes memberType = member.MemberType;

                    m_DynamicQuery.AddWhere(valueName);

                    Func<object> funclet;
                    switch (memberType)
                    {
                        case MemberTypes.Field:
                            FieldInfo f = (FieldInfo)member;
                            ConstantExpression c = (ConstantExpression)e;
                            funclet = (() => f.GetValue(c.Value));
                            break;

                        case MemberTypes.Property:
                            PropertyInfo propInfo = (PropertyInfo)member;
                            ConstantExpression c2 = (ConstantExpression)e;
                            funclet = (() => propInfo.GetGetMethod(false).Invoke(c2.Value, null));
                            break;

                        default:
                            throw new NotSupportedException(string.Format("Accessing member of type '{0}' is not supported.", memberType));
                    }

                    KeyValuePair<string, Func<object>> kvp = new KeyValuePair<string, Func<object>>(valueName, funclet);
                    m_Funclets.Add(kvp);
                }
                else
                {
                    TableProperty tableProperty = table.Properties.Where(p => p.PropertyName == memberName).SingleOrDefault();
                    if (tableProperty == null)
                        throw new Exception(string.Format("Property '{0}' not found in table '{1}'.", memberName, table));
                    TableColumn column = tableProperty.Column;
                    if (column == null)
                        throw new Exception(string.Format("Property '{0}' in table '{1}' does not have an associated column.", memberName, table));

                    Container container = AddTablesRecursively(table, memberExpression);

                    Property property = m_DynamicQuery.AddProperty(container, column, nodeType);

                    if (!m_IsSelect)
                    {
                        m_DynamicQuery.AddWhere(property);
                    }
                }
            }
            else
            {
                if (nodeType != ExpressionType.Constant)
                {
                    throw new NotSupportedException(string.Format("Accessing member '{0}' is not supported in this context.", member.Name));
                }
                string valueName = string.Format("@p{0}", m_Funclets.Count);
                MemberTypes memberType = member.MemberType;

                m_DynamicQuery.AddWhere(valueName);

                Func<object> funclet;
                switch (memberType)
                {
                    case MemberTypes.Field:
                        FieldInfo f = (FieldInfo)member;
                        ConstantExpression c = (ConstantExpression)e;
                        funclet = (() => f.GetValue(c.Value));
                        break;

                    case MemberTypes.Property:
                        PropertyInfo propInfo = (PropertyInfo)member;
                        ConstantExpression c2 = (ConstantExpression)e;
                        funclet = (() => propInfo.GetGetMethod(false).Invoke(c2.Value, null));
                        break;

                    default:
                        throw new NotSupportedException(string.Format("Accessing member of type '{0}' is not supported.", memberType));
                }

                KeyValuePair<string, Func<object>> kvp = new KeyValuePair<string, Func<object>>(valueName, funclet);
                m_Funclets.Add(kvp);
            }
        }

        private Container AddTablesRecursively(Table inTable, MemberExpression expression)
        {
            Expression e = expression.Expression;
            ExpressionType nodeType = e.NodeType;
            if (nodeType == ExpressionType.MemberAccess)
            {
                MemberExpression memberExpression = (MemberExpression)e;

                MemberInfo member = expression.Member;

                Type declaringType = member.DeclaringType;
                string memberName = member.Name;

                Table sourceTable = m_Schema.Tables.Where(p => p.FullClassName == declaringType.FullName).SingleOrDefault();
                if (sourceTable == null)
                    throw new Exception(string.Format("Table '{0}' not found.", declaringType.FullName));
                TableProperty tableProperty = sourceTable.Properties.Where(p => p.PropertyName == memberName).SingleOrDefault();
                if (tableProperty == null)
                    throw new Exception(string.Format("Property '{0}' not found in table '{1}'.", memberName, sourceTable));
                TableColumn sourceColumn = tableProperty.Column;
                if (sourceColumn == null)
                    throw new Exception(string.Format("Property '{0}' in table '{1}' does not have an associated column.", memberName, sourceTable));

                Container container = AddTablesRecursively(sourceTable, memberExpression);
                Property property = m_DynamicQuery.AddProperty(container, sourceColumn, nodeType);

                container = m_DynamicQuery.AddContainer(property, inTable);

                return container;
            }
            else
            {
                MemberInfo member = expression.Member;

                Type declaringType = member.DeclaringType;
                string memberName = member.Name;

                Table sourceTable = m_Schema.Tables.Where(p => p.FullClassName == declaringType.FullName).SingleOrDefault();
                if (sourceTable == null)
                    throw new Exception(string.Format("Table of type '{0}' not found.", declaringType.FullName));

                TableProperty tableProperty = sourceTable.Properties.Where(p => p.PropertyName == memberName).SingleOrDefault();
                if (tableProperty == null)
                    throw new Exception(string.Format("Property '{0}' not found in table '{1}'.", memberName, sourceTable));

                Property property;
                Container container;
                TableColumn sourceColumn = tableProperty.Column;
                if (sourceColumn == null)
                {
                    // sourceColumn can be null for a property which is a child
                    // property.. eg, referencing table has unique FK to sourceTable
                    if (tableProperty.IsChildContainer)
                    {
                        // things I know:
                        // - the property is a "virtual property"
                        // - the property is a "reverse foreign key"
                        // - the property does not have a column associated with it
                        //   because the column is the table's primary key
                        // - we need to do a join we normally would not do, because
                        //   the structure implies 'child list' but a unique constraint
                        //   on the FK column results in it being a 1:1 or 1:0 
                        //   relationship.
                        // - inTable = referencing table (contains unique FK)
                        // - sourceTable = referenced table
                        // - tableProperty = sourceTable's property
                        // - calling function is expecting container of 'inTable'

                        List<Property> props = m_DynamicQuery.GetPropertiesBeforeQuery(m_MainContainer).ToList();
                        Property pkProp = props.Where(p => p.TableColumn.IsKey).Single();

                        TableColumn joinColumn = inTable.Columns.Where(p => p.ColumnName == tableProperty.ChildColumnName).Single();

                        property = m_DynamicQuery.AddProperty(m_MainContainer, pkProp.TableColumn, nodeType);
                        container = m_DynamicQuery.AddContainer(property, inTable, joinColumn);
                    }
                    else
                    {
                        throw new Exception(string.Format("Property '{0}' in table '{1}' does not have an associated column.", memberName, sourceTable));
                    }
                }
                else
                {
                    property = m_DynamicQuery.AddProperty(m_MainContainer, sourceColumn, nodeType);
                    container = m_DynamicQuery.AddContainer(property, inTable);
                }

                return container;
            }
        }

        private void ExpandUnary(UnaryExpression u, string op)
        {
            int test;
            bool isInteger = int.TryParse(u.Operand.ToString(), out test);

            if (!string.IsNullOrEmpty(op))
            {
                throw new NotImplementedException();
            }

            if (!isInteger) m_DynamicQuery.AddWhere("(");
            ExpandExpression(u.Operand);
            if (!isInteger) m_DynamicQuery.AddWhere(")");
        }

        /// <summary>
        /// Returns an enumerator that iterates through the collection.
        /// </summary>
        /// <returns>A <see cref="T:System.Collections.Generic.IEnumerator`1"/> that can be used to iterate through the collection.</returns>
        public IEnumerator<T> GetEnumerator()
        {
            List<T> tuples = new List<T>();

            if (m_Schema.Provider == "SQL Server")
            {
                using (SqlConnection connection = new SqlConnection(m_ConnectionString))
                {
                    connection.Open();

                    string commandText = m_DynamicQuery.GetQuery();

                    SqlParameter[] p = EvaluateFunclets<SqlParameter>();
                    DataTable dt = DataHelper.GetDataTable(connection, commandText, p);

                    // TODO - never got data loading via dynamic assembly fully working.
                    // load time on a lot of rows with a lot of FK's is pretty bad.
                    /*if (dt.Rows.Count > 5000)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            T item = new T();
                            item.LoadFromDataRowCompiled(m_DynamicQuery, dr);
                            tuples.Add(item);
                        }                    
                    }
                    else*/
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            T item = new T();
                            item.LoadFromDataRow(m_DynamicQuery, dr);
                            tuples.Add(item);
                        }
                    }
                }
            }
            else if (m_Schema.Provider == "SQL Server CE")
            {
                using (SqlCeConnection connection = new SqlCeConnection(m_ConnectionString))
                {
                    connection.Open();

                    string commandText = m_DynamicQuery.GetQuery();

                    SqlCeParameter[] p = EvaluateFunclets<SqlCeParameter>();
                    DataTable dt = SqlCeDataHelper.GetDataTable(connection, commandText, p);

                    // TODO - never got data loading via dynamic assembly fully working.
                    // load time on a lot of rows with a lot of FK's is pretty bad.
                    /*if (dt.Rows.Count > 5000)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            T item = new T();
                            item.LoadFromDataRowCompiled(m_DynamicQuery, dr);
                            tuples.Add(item);
                        }                    
                    }
                    else*/
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            T item = new T();
                            item.LoadFromDataRow(m_DynamicQuery, dr);
                            tuples.Add(item);
                        }
                    }
                }
            }
            else
            {
                throw new Exception("Unknown provider");
            }
            return tuples.GetEnumerator();
        }

        IEnumerator IEnumerable.GetEnumerator()
        {
            return GetEnumerator();
        }

        Type IQueryable.ElementType
        {
            get
            {
                // TODO: what should this be? left over from some starter code
                return typeof(FileInfo);
            }
        }

        Expression IQueryable.Expression
        {
            get
            {
                return Expression.Constant(this);
            }
        }

        IQueryProvider IQueryable.Provider
        {
            get
            {
                return this;
            }
        }

        /// <summary>
        /// Gets a new binding list containing all records in the table.
        /// </summary>
        /// <returns>A new binding list containing all records in the table.</returns>
        public BindingList<T> GetNewBindingList()
        {
            return new BindingList<T>(this.ToList());
        }
    }
}
